* Apply to:  sf_bos_2009_2011_divided.xls
* Description:  Format Board of Supervisor Roll Calls

clear
set more off


import excel "C:\Users\Scott\Documents\1801 SF BoS\sf_bos_2009_2011_divided.xls", sheet("Sheet1") firstrow


* Eliminate duplicate roll calls (i.e., passed on first reading)

sort fileno sequence

gen double lagfileno = fileno[_n+1]
gen double diffileno = fileno - lagfileno

list date fileno sequence type disposition ayes nays if diffileno==0 & disposition=="PASSED ON FIRST READING"
list date fileno sequence type disposition ayes nays if diffileno==0 & disposition=="PASSED ON FIRST READING AS AMENDED"
list date fileno sequence type disposition ayes nays if diffileno==0 & disposition=="PASSED ON FIRST READING AS DIVIDED"

drop if diffileno==0 & disposition=="PASSED ON FIRST READING"
drop if diffileno==0 & disposition=="PASSED ON FIRST READING AS AMENDED"
drop if diffileno==0 & disposition=="PASSED ON FIRST READING AS DIVIDED"

drop lagfileno diffileno

drop if rescind==1


* Supervisor votes

gen y_aliotopier = strmatch(aye_names, "*Alioto-Pier*")
gen n_aliotopier = strmatch(nay_names, "*Alioto-Pier*")
gen a_aliotopier = strmatch(abs_names, "*Alioto-Pier*")
gen e_aliotopier = strmatch(exc_names, "*Alioto-Pier*")

gen y_avalos = strmatch(aye_names, "*Avalos*")
gen n_avalos = strmatch(nay_names, "*Avalos*")
gen a_avalos = strmatch(abs_names, "*Avalos*")
gen e_avalos = strmatch(exc_names, "*Avalos*")

gen y_campos = strmatch(aye_names, "*Campos*")
gen n_campos = strmatch(nay_names, "*Campos*")
gen a_campos = strmatch(abs_names, "*Campos*")
gen e_campos = strmatch(exc_names, "*Campos*")

gen y_chiu = strmatch(aye_names, "*Chiu*")
gen n_chiu = strmatch(nay_names, "*Chiu*")
gen a_chiu = strmatch(abs_names, "*Chiu*")
gen e_chiu = strmatch(exc_names, "*Chiu*")

gen y_chu = strmatch(aye_names, "*Chu*")
gen n_chu = strmatch(nay_names, "*Chu*")
gen a_chu = strmatch(abs_names, "*Chu*")
gen e_chu = strmatch(exc_names, "*Chu*")

gen y_daly = strmatch(aye_names, "*Daly*")
gen n_daly = strmatch(nay_names, "*Daly*")
gen a_daly = strmatch(abs_names, "*Daly*")
gen e_daly = strmatch(exc_names, "*Daly*")

gen y_dufty = strmatch(aye_names, "*Dufty*")
gen n_dufty = strmatch(nay_names, "*Dufty*")
gen a_dufty = strmatch(abs_names, "*Dufty*")
gen e_dufty = strmatch(exc_names, "*Dufty*")

gen y_elsbernd = strmatch(aye_names, "*Elsbernd*")
gen n_elsbernd = strmatch(nay_names, "*Elsbernd*")
gen a_elsbernd = strmatch(abs_names, "*Elsbernd*")
gen e_elsbernd = strmatch(exc_names, "*Elsbernd*")

gen y_mar = strmatch(aye_names, "*Mar*")
gen n_mar = strmatch(nay_names, "*Mar*")
gen a_mar = strmatch(abs_names, "*Mar*")
gen e_mar = strmatch(exc_names, "*Mar*")

gen y_maxwell = strmatch(aye_names, "*Maxwell*")
gen n_maxwell = strmatch(nay_names, "*Maxwell*")
gen a_maxwell = strmatch(abs_names, "*Maxwell*")
gen e_maxwell = strmatch(exc_names, "*Maxwell*")

gen y_mirkarimi = strmatch(aye_names, "*Mirkarimi*")
gen n_mirkarimi = strmatch(nay_names, "*Mirkarimi*")
gen a_mirkarimi = strmatch(abs_names, "*Mirkarimi*")
gen e_mirkarimi = strmatch(exc_names, "*Mirkarimi*")


gen v_aliotopier = 0
replace v_aliotopier = 1 if y_aliotopier==1
replace v_aliotopier = 6 if n_aliotopier==1
replace v_aliotopier = 9 if a_aliotopier==1 | e_aliotopier==1

gen v_avalos = 0
replace v_avalos = 1 if y_avalos==1
replace v_avalos = 6 if n_avalos==1
replace v_avalos = 9 if a_avalos==1 | e_avalos==1

gen v_campos = 0
replace v_campos = 1 if y_campos==1
replace v_campos = 6 if n_campos==1
replace v_campos = 9 if a_campos==1 | e_campos==1

gen v_chiu = 0
replace v_chiu = 1 if y_chiu==1
replace v_chiu = 6 if n_chiu==1
replace v_chiu = 9 if a_chiu==1 | e_chiu==1

gen v_chu = 0
replace v_chu = 1 if y_chu==1
replace v_chu = 6 if n_chu==1
replace v_chu = 9 if a_chu==1 | e_chu==1

gen v_daly = 0
replace v_daly = 1 if y_daly==1
replace v_daly = 6 if n_daly==1
replace v_daly = 9 if a_daly==1 | e_daly==1

gen v_dufty = 0
replace v_dufty = 1 if y_dufty==1
replace v_dufty = 6 if n_dufty==1
replace v_dufty = 9 if a_dufty==1 | e_dufty==1

gen v_elsbernd = 0
replace v_elsbernd = 1 if y_elsbernd==1
replace v_elsbernd = 6 if n_elsbernd==1
replace v_elsbernd = 9 if a_elsbernd==1 | e_elsbernd==1

gen v_mar = 0
replace v_mar = 1 if y_mar==1
replace v_mar = 6 if n_mar==1
replace v_mar = 9 if a_mar==1 | e_mar==1

gen v_maxwell = 0
replace v_maxwell = 1 if y_maxwell==1
replace v_maxwell = 6 if n_maxwell==1
replace v_maxwell = 9 if a_maxwell==1 | e_maxwell==1

gen v_mirkarimi = 0
replace v_mirkarimi = 1 if y_mirkarimi==1
replace v_mirkarimi = 6 if n_mirkarimi==1
replace v_mirkarimi = 9 if a_mirkarimi==1 | e_mirkarimi==1


* Transpose vote matrix to obtain roll call file

sort year sequence

gen rcno = _n


keep rcno v_aliotopier v_avalos v_campos v_chiu v_chu v_daly ///
     v_dufty v_elsbernd v_mar v_maxwell v_mirkarimi

order rcno v_aliotopier v_avalos v_campos v_chiu v_chu v_daly ///
     v_dufty v_elsbernd v_mar v_maxwell v_mirkarimi

xpose, clear varname


* Add supervisor information

gen name = ""
replace name = "M_AliotoPier" if _varname=="v_aliotopier"
replace name = "J_Avalos" if _varname=="v_avalos"
replace name = "D_Campos" if _varname=="v_campos"
replace name = "D_Chiu" if _varname=="v_chiu"
replace name = "C_Chu" if _varname=="v_chu"
replace name = "C_Daly" if _varname=="v_daly"
replace name = "B_Dufty" if _varname=="v_dufty"
replace name = "S_Elsbernd" if _varname=="v_elsbernd"
replace name = "E_Mar" if _varname=="v_mar"
replace name = "S_Maxwell" if _varname=="v_maxwell"
replace name = "R_Mirkarimi" if _varname=="v_mirkarimi"

drop if _varname=="rcno"
drop _varname

gen district = 0
replace district = 1 if name=="E_Mar"
replace district = 2 if name=="M_AliotoPier"
replace district = 3 if name=="D_Chiu"
replace district = 4 if name=="C_Chu"
replace district = 5 if name=="R_Mirkarimi"
replace district = 6 if name=="C_Daly"
replace district = 7 if name=="S_Elsbernd"
replace district = 8 if name=="B_Dufty"
replace district = 9 if name=="D_Campos"
replace district = 10 if name=="S_Maxwell"
replace district = 11 if name=="J_Avalos"


gen faction = "None"

replace faction = "Progressive" if name=="J_Avalos"
replace faction = "Progressive" if name=="D_Campos"
replace faction = "Progressive" if name=="D_Chiu"
replace faction = "Progressive" if name=="C_Daly"
replace faction = "Progressive" if name=="E_Mar"
replace faction = "Progressive" if name=="S_Maxwell"
replace faction = "Progressive" if name=="R_Mirkarimi"

replace faction = "Moderate" if name=="M_AliotoPier"
replace faction = "Moderate" if name=="C_Chu"
replace faction = "Moderate" if name=="S_Elsbernd"
replace faction = "Moderate" if name=="B_Dufty"


gen idno = 10000 + _n

order idno name district faction


export delimited using "C:\Users\Scott\Documents\1802 AJPS\Figure A2 and A3\sfbos_1.csv", replace

* End
